using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using DataInfo;
using XBLib.Dal;
using XBLib.Dal.AdoDal;
using XBLib.BusinessObject;
using System.Data.Common;
using KenLib.Common;
using KenLib.BusinessManager;

namespace KenLib.Dal
{
    public class 基本情報Dao : BaseAdoDao
    {
        private string m_requestTime;
        private string m_displayTime;

        /// <summary>
        /// SelectHumanListByValue
        /// </summary>
        /// <param name="type"></param>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="pageTotal"></param>
        /// <param name="strSort"></param>
        /// <returns></returns>
        public DataTable SelectHumanListByValue(int type, string name, int value, int pageIndex, int pageSize, ref int pageTotal, string strSort, string strSortdic)
        {
            DataTable dt = new DataTable();
            try
            {
                m_requestTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                DbParameter param = _daoHelper.CreateParameter("@type", type, DbType.Int32);
                DbParameter param1 = _daoHelper.CreateParameter("@name", name, DbType.String);
                DbParameter param2 = _daoHelper.CreateParameter("@value", value, DbType.Int32);
                DbParameter paramPageIndex = _daoHelper.CreateParameter("@pageIndex", pageIndex, DbType.Int32);
                DbParameter paramPageSize = _daoHelper.CreateParameter("@PageSize", pageSize, DbType.Int32);
                DbParameter paramSort = _daoHelper.CreateParameter("@Sort", strSort, DbType.String);
                DbParameter paramSortdic = _daoHelper.CreateParameter("@Sortdic", strSortdic, DbType.String);
                DbCommand dbcommand = _daoHelper.CreateCommand(CommandNameManager.Select_HumanListByValue, CommandType.StoredProcedure, param, param1, param2, paramPageIndex, paramPageSize, paramSort, paramSortdic);
               
                dt = _daoHelper.Query(dbcommand);
                if (dt.Rows.Count > 0)
                    if (dt.Columns["total"] != null)
                        pageTotal = Convert.ToInt32(dt.Rows[0]["total"].ToString());
                m_displayTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                ExportFile.WriteLog(type.ToString(), "", "SELECT", "Select_HumanListByValue", m_requestTime, m_displayTime);
            }
            catch (Exception ex)
            {
                m_displayTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                ExportFile.WriteErr(type.ToString(), "", "SELECT", ex.Message, m_requestTime, m_displayTime);
                throw new LocalException(ex.Message, ex);
            }
            return dt;
        }
        /// <summary>
        /// Inset
        /// </summary>
        /// <param name="en"></param>
        public int Insert基本情報(基本情報 en)
        {
            /*
             * @人コード ,@姓 ,@名 ,@フリガナ姓 ,
             * @フリガナ名 ,@英字姓 ,@英字名 ,@旧姓漢字 ,@旧姓フリガナ ,@性別 ,@生年月日 ,
	         *   @写真 ,@国籍 ,@出身地 ,@血液型コード ,@血液型名 ,@会社携帯 ,@会社メールアドレス１ ,@会社メールアドレス２ ,
	         *  @趣味 ,@外国人登録番号 ,@在留期限 ,@滞在予定期間 ,@パスポート番号 ,@発行日 ,@有効期限 ,@市民権 
             * 
             * 
             */
            int i = 0;
            try
            {
                m_requestTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");

                DbParameter param1 = _daoHelper.CreateParameter("@人コード", en.人コード);
                DbParameter param2 = _daoHelper.CreateParameter("@姓", en.姓);
                DbParameter param3 = _daoHelper.CreateParameter("@名", en.名);
                DbParameter param4 = _daoHelper.CreateParameter("@フリガナ姓", en.フリガナ姓);
                DbParameter param5 = _daoHelper.CreateParameter("@フリガナ名", en.フリガナ名);
                DbParameter param6 = _daoHelper.CreateParameter("@英字姓", en.英字姓);
                DbParameter param7 = _daoHelper.CreateParameter("@英字名", en.英字名);
                DbParameter param8 = _daoHelper.CreateParameter("@旧姓漢字", en.旧漢字);
                DbParameter param9 = _daoHelper.CreateParameter("@旧姓フリガナ", en.旧姓フリガナ);
                DbParameter param10 = _daoHelper.CreateParameter("@性別", en.性別);
                DbParameter param11 = _daoHelper.CreateParameter("@生年月日", en.生年月日);
                DbParameter param12 = _daoHelper.CreateParameter("@写真", en.写真);
                DbParameter param13 = _daoHelper.CreateParameter("@国籍", en.国籍);
                DbParameter param14 = _daoHelper.CreateParameter("@出身地", en.出身地);
                DbParameter param15 = _daoHelper.CreateParameter("@血液型コード", en.血液型コード);
                DbParameter param16 = _daoHelper.CreateParameter("@血液型名", en.血液型名);
                DbParameter param17 = _daoHelper.CreateParameter("@会社携帯", en.会社携帯);
                DbParameter param18 = _daoHelper.CreateParameter("@会社メールアドレス１", en.会社メールアドレス１);
                DbParameter param19 = _daoHelper.CreateParameter("@会社メールアドレス２", en.会社メールアドレス２);
                DbParameter param20 = _daoHelper.CreateParameter("@趣味", en.趣味);
                DbParameter param21 = _daoHelper.CreateParameter("@外国人登録番号", en.外国人登録番号);
                DbParameter param22 = _daoHelper.CreateParameter("@在留期限", en.在留期限);
                DbParameter param23 = _daoHelper.CreateParameter("@滞在予定期間", en.滞在予定期間);
                DbParameter param24 = _daoHelper.CreateParameter("@パスポート番号", en.パスポート番号);
                DbParameter param25 = _daoHelper.CreateParameter("@発行日", en.発行日);
                DbParameter param26 = _daoHelper.CreateParameter("@有効期限", en.有効期限);
                DbParameter param27 = _daoHelper.CreateParameter("@市民権", en.市民権);
                DbParameter param28 = _daoHelper.CreateParameter("@企業ID", en.企業ＩＤ);
                DbCommand commands = _daoHelper.CreateCommand(CommandNameManager.sp_Insert基本情報, CommandType.StoredProcedure);

                commands.Parameters.Add(param1);
                commands.Parameters.Add(param2);
                commands.Parameters.Add(param3);
                commands.Parameters.Add(param4);
                commands.Parameters.Add(param5);
                commands.Parameters.Add(param6);
                commands.Parameters.Add(param7);
                commands.Parameters.Add(param8);
                commands.Parameters.Add(param9);
                commands.Parameters.Add(param10);
                commands.Parameters.Add(param11);
                commands.Parameters.Add(param12);
                commands.Parameters.Add(param13);
                commands.Parameters.Add(param14);
                commands.Parameters.Add(param15);
                commands.Parameters.Add(param16);
                commands.Parameters.Add(param17);
                commands.Parameters.Add(param18);
                commands.Parameters.Add(param19);
                commands.Parameters.Add(param20);
                commands.Parameters.Add(param21);
                commands.Parameters.Add(param22);
                commands.Parameters.Add(param23);
                commands.Parameters.Add(param24);
                commands.Parameters.Add(param25);
                commands.Parameters.Add(param26);
                commands.Parameters.Add(param27);
                commands.Parameters.Add(param28);

                i = _daoHelper.Execute(commands);
                m_displayTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                ExportFile.WriteLog("", "", "INSERT", "Insert基本情報", m_requestTime, m_displayTime);

            }
            catch (Exception ex)
            {
                m_displayTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                ExportFile.WriteErr("", "", "INSERT", ex.Message, m_requestTime, m_displayTime);


            }
            return i;
        }

        public string Select社員ID_MAX()
        {

            DbCommand dbcommand = _daoHelper.CreateCommand(CommandNameManager.sp_Select社員ID_MAX, CommandType.StoredProcedure);
            DataTable dt = _daoHelper.Query(dbcommand);
            
            return dt.Rows[0][0].ToString();
            //return new 基本情報Dao().Select社員ID_MAX();
        }

        #region Hunglt
        public DataTable SelectAll基本情報(int 社員ID)
        {
            DbParameter param社員ID = _daoHelper.CreateParameter("@社員ID", 社員ID, DbType.Int32);

            DbCommand dbcommand = _daoHelper.CreateCommand("sp_select_基本情報_Hunglt", CommandType.StoredProcedure, param社員ID);
            return _daoHelper.Query(dbcommand);
        }
        public DataTable SelectFormJ_基本情報(int 社員ID)
        {
            DbParameter param社員ID = _daoHelper.CreateParameter("@社員ID", 社員ID, DbType.Int32);

            DbCommand dbcommand = _daoHelper.CreateCommand("sp_select_基本情報_FormJ_Hunglt", CommandType.StoredProcedure, param社員ID);
            return _daoHelper.Query(dbcommand);
        }
        public void UpdateInfo基本情報(基本情報 obj)
        {
            DbParameter dbParam人コード = _daoHelper.CreateParameter("@人コード", obj.人コード, DbType.String);
            DbParameter dbParam血液型コード = _daoHelper.CreateParameter("@血液型コード", obj.血液型コード, DbType.String);
            DbParameter dbParam血液型名 = _daoHelper.CreateParameter("@血液型名", obj.血液型名, DbType.String);
			DbParameter dbParam現資格 = _daoHelper.CreateParameter("@現資格", obj.現資格, DbType.String);
            DbParameter dbParam査証申請地 = _daoHelper.CreateParameter("@査証申請地", obj.査証申請地, DbType.String);
            DbParameter dbParam入国目的 = _daoHelper.CreateParameter("@入国目的", obj.入国目的, DbType.Int32);
            DbParameter dbParamパスポート番号 = _daoHelper.CreateParameter("@パスポート番号", obj.パスポート番号, DbType.String);
            DbParameter dbParam発行日 = _daoHelper.CreateParameter("@発行日", obj.発行日, DbType.DateTime);
            DbParameter dbParam有効期限 = _daoHelper.CreateParameter("@有効期限", obj.有効期限, DbType.DateTime);
            DbParameter dbParam実習生在留資格タイプ = _daoHelper.CreateParameter("@実習生在留資格タイプ", obj.実習生在留資格タイプ, DbType.Boolean);
            DbParameter dbParam実習生入国グループ = _daoHelper.CreateParameter("@実習生入国グループ", obj.実習生入国グループ, DbType.String);
            DbParameter param社員ID = _daoHelper.CreateParameter("@社員ID", obj.社員ID, DbType.Int32);

            DbCommand dbcommand = _daoHelper.CreateCommand("sp_update_基本情報_FormJ_Hunglt", CommandType.StoredProcedure,
				dbParam人コード, dbParam血液型コード, dbParam血液型名, dbParam現資格, dbParam査証申請地, dbParam入国目的,
                dbParamパスポート番号, dbParam発行日, dbParam有効期限, dbParam実習生在留資格タイプ, dbParam実習生入国グループ, param社員ID);
            _daoHelper.Execute(dbcommand);
        }
            public void Update基本情報_For_実習生選択情報ID(int 社員ID) {
            DbParameter param社員ID = _daoHelper.CreateParameter("@社員ID", 社員ID, DbType.Int32);

            DbCommand dbcommand = _daoHelper.CreateCommand("sp_update_基本情報_Hunglt", CommandType.StoredProcedure, param社員ID);
            _daoHelper.Execute(dbcommand);
        }
        public DataTable Get_社員ID_By_入国ＧID(int グループID)
        {
            DbParameter paramグループID = _daoHelper.CreateParameter("@グループID", グループID, DbType.Int32);

            DbCommand dbcommand = _daoHelper.CreateCommand("sp_select_入国ＧID_TraineeGroupCheck_Hunglt", CommandType.StoredProcedure, paramグループID);
            return _daoHelper.Query(dbcommand);
        }
        public DataTable Select_Exists_入学情報(int グループID) {
            DbParameter paramグループID = _daoHelper.CreateParameter("@グループID", グループID, DbType.Int32);

            DbCommand dbcommand = _daoHelper.CreateCommand("select_frmJ_TraineeGroupInput_Hunglt", CommandType.StoredProcedure, paramグループID);
            return _daoHelper.Query(dbcommand);
        }
        public DataTable Select入国目的()
        {
            DbCommand dbcommand = _daoHelper.CreateCommand("sp_select_入国目的_Hunglt", CommandType.StoredProcedure);
            return _daoHelper.Query(dbcommand);
        }
        public int Insert_All_Transaction(int グループID, 実習生選択情報 obj_実習生選択情報, 雇用情報 obj_雇用情報, 出入国歴情報 obj_出入国歴情報, 入国Ｇ obj_入国Ｇ)
        {
            DbConnection conn = _daoHelper.GetConnection();
            conn.Open();
            DbTransaction trs = conn.BeginTransaction();
            try
            {
                実習生選択情報Manager frm_実習生選択情報 = new 実習生選択情報Manager();

                雇用情報Manager frm_雇用情報 = new 雇用情報Manager();

                出入国歴情報Manager frm_出入国歴情報 = new 出入国歴情報Manager();

                入国ＧManager frm_入国Ｇ = new 入国ＧManager();

                DataTable dt_社員ID = this.Get_社員ID_By_入国ＧID(グループID);
                if (dt_社員ID.Rows.Count > 0)
                {
                    frm_実習生選択情報.InsertInfo実習生選択情報(obj_実習生選択情報);

                    frm_入国Ｇ.UpdateInfo入国Ｇ(グループID, obj_入国Ｇ);

                    for (int i = 0; i < dt_社員ID.Rows.Count; i++)
                    {
                        int 社員ID = Int32.Parse(dt_社員ID.Rows[i]["社員ID"].ToString());

                        frm_雇用情報.InsertInfo雇用情報(obj_雇用情報, 社員ID);

                        frm_出入国歴情報.InsertInfo出入国歴情報(obj_出入国歴情報, 社員ID);

                        this.Update基本情報_For_実習生選択情報ID(社員ID);
                    }
                }
                trs.Commit();
            }
            catch (Exception ex)
            {
                trs.Rollback();
                throw new Exception(ex.Message);
            }
            conn.Close();
            return 0;
        }
        public int Update_All_Transaction(int グループID, 実習生選択情報 obj_実習生選択情報, 雇用情報 obj_雇用情報, 出入国歴情報 obj_出入国歴情報, 入国Ｇ obj_入国Ｇ)
        {
            DbConnection conn = _daoHelper.GetConnection();
            conn.Open();
            DbTransaction trs = conn.BeginTransaction();
            try
            {
                実習生選択情報Manager frm_実習生選択情報 = new 実習生選択情報Manager();

                雇用情報Manager frm_雇用情報 = new 雇用情報Manager();

                出入国歴情報Manager frm_出入国歴情報 = new 出入国歴情報Manager();

                入国ＧManager frm_入国Ｇ = new 入国ＧManager();

                DataTable dt_社員ID = this.Get_社員ID_By_入国ＧID(グループID);
                if (dt_社員ID.Rows.Count > 0)
                {
                    frm_実習生選択情報.UpdateInfo実習生選択情報(obj_実習生選択情報, Int32.Parse(dt_社員ID.Rows[0]["社員ID"].ToString()));
                    frm_入国Ｇ.UpdateInfo入国Ｇ(グループID, obj_入国Ｇ);       
          
                    for (int i = 0; i < dt_社員ID.Rows.Count; i++)
                    {
                        int 社員ID = Int32.Parse(dt_社員ID.Rows[i]["社員ID"].ToString());

                        frm_雇用情報.UpdateInfo雇用情報(obj_雇用情報, 社員ID);

                        frm_出入国歴情報.UpdateInfo出入国歴情報(obj_出入国歴情報, 社員ID);
                    }
                }
                trs.Commit();
            }
            catch (Exception ex)
            {
                trs.Rollback();
                throw new Exception(ex.Message);
            }
            conn.Close();
            return 0;
        }
        #endregion

        #region TUNG
        public DataTable SearchTraineeList(int 企業ID , int 検索 , string name)
        {
            DbParameter dbParam1 = _daoHelper.CreateParameter("@企業ID", 企業ID, DbType.Int32);
            DbParameter dbParam2 = _daoHelper.CreateParameter("@検索", 検索, DbType.Int32);
            DbParameter dbParam3 = _daoHelper.CreateParameter("@Name", name, DbType.String);
            DbCommand dbcommand = _daoHelper.CreateCommand("sp_SearchTraineeList_TUNG", CommandType.StoredProcedure, dbParam1,dbParam2,dbParam3);
            return _daoHelper.Query(dbcommand);


        }

        public DataTable get基本情報TrainneBasicInfo(int 社員ID)
        {
            DbParameter dbParam1 = _daoHelper.CreateParameter("@社員ID", 社員ID, DbType.Int32);
            DbCommand dbcommand = _daoHelper.CreateCommand("sp_get基本情報TraineeBasicInfo_TUNG", CommandType.StoredProcedure, dbParam1);
            return _daoHelper.Query(dbcommand);
        }



        #endregion

        #region Mai
      
        public DataTable sp_frmJ_TraineeEnterSchoolSearch_SEARCH基本情報(基本情報 obj)
        {
            DbParameter param = _daoHelper.CreateParameter("@入国ＧID", obj.入国ＧID, DbType.Int32);
            DbParameter param1 = _daoHelper.CreateParameter("@団体ID", obj.団体ID, DbType.Int32);
            DbParameter param2 = _daoHelper.CreateParameter("@企業ＩＤ", obj.企業ＩＤ, DbType.Int32);
            DbCommand dbcommand = _daoHelper.CreateCommand("sp_frmJ_TraineeEnterSchoolSearch_SEARCH基本情報_Mai", CommandType.StoredProcedure, param, param1, param2);
            return _daoHelper.Query(dbcommand);
        }
    #endregion
        #region phuong
        public DataTable GetQuocGia(Int32 @quocgia)
        {

            DbParameter dbParam2 = _daoHelper.CreateParameter("@quocgia", @quocgia, DbType.Int32);

            DbCommand dbcommand = _daoHelper.CreateCommand("sp_frmJ_StaffBasicInfoInput_GetQuocGia_Phuong", CommandType.StoredProcedure, dbParam2);
            return _daoHelper.Query(dbcommand);
        }
        public DataTable Get基本情報( Int32 社員ID)
        {
           
            DbParameter dbParam2 = _daoHelper.CreateParameter("@社員ID", 社員ID, DbType.Int32);
            DbCommand dbcommand = _daoHelper.CreateCommand(CommandNameManager.sp_frmJ_StaffBasicInfoInput_Get_Phuong, CommandType.StoredProcedure,dbParam2);
            return _daoHelper.Query(dbcommand);
        }
        public DataTable GetAll基本情報()
        {

            DbCommand dbcommand = _daoHelper.CreateCommand(CommandNameManager.sp_frmJ_StaffBasicInfoInput_GetAll_Phuong, CommandType.StoredProcedure);
            return _daoHelper.Query(dbcommand);
        }
        public void Update基本情報(基本情報 obj)
        {
            DbParameter dbParam1 = _daoHelper.CreateParameter("@企業ID", obj.企業ＩＤ, DbType.Int32);
            DbParameter dbParam2 = _daoHelper.CreateParameter("@社員ID ", obj.社員ID, DbType.Int32);
            DbParameter dbParam3 = _daoHelper.CreateParameter("@人コード", obj.人コード, DbType.String);
            DbParameter dbParam4 = _daoHelper.CreateParameter("@姓", obj.姓, DbType.String);
            DbParameter dbParam5 = _daoHelper.CreateParameter("@名", obj.名, DbType.String);
            //////////////////////////////////////////////////////////////////////////////////////////////////
            DbParameter dbParam6 = _daoHelper.CreateParameter("@フリガナ姓", obj.フリガナ姓, DbType.String);
            DbParameter dbParam7 = _daoHelper.CreateParameter("@フリガナ名", obj.フリガナ名, DbType.String);
            DbParameter dbParam8 = _daoHelper.CreateParameter("@英字姓", obj.英字姓, DbType.String);
            DbParameter dbParam9 = _daoHelper.CreateParameter("@英字名", obj.英字名, DbType.String);
            DbParameter dbParam10 = _daoHelper.CreateParameter("@旧漢字", obj.旧漢字, DbType.String);
            /////////////////////////////////////////////////////////////////////////////////////////////////////
            DbParameter dbParam11 = _daoHelper.CreateParameter("@旧姓フリガナ", obj.旧姓フリガナ, DbType.String);
            DbParameter dbParam12 = _daoHelper.CreateParameter("@性別", obj.性別, DbType.Boolean);// 
            DbParameter dbParam13 = _daoHelper.CreateParameter("@生年月日", obj.生年月日, DbType.DateTime);
            DbParameter dbParam37 = _daoHelper.CreateParameter("@年齢", obj.年齢, DbType.Int32);
            DbParameter dbParam14 = _daoHelper.CreateParameter("@写真", obj.写真, DbType.String);
            DbParameter dbParam38 = _daoHelper.CreateParameter("@国籍", obj.国籍, DbType.Int32);

            DbParameter dbParam15 = _daoHelper.CreateParameter("@出身地", obj.出身地, DbType.String);
            ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
            DbParameter dbParam16 = _daoHelper.CreateParameter("@血液型コード", obj.血液型コード, DbType.String);
            DbParameter dbParam17 = _daoHelper.CreateParameter("@血液型名", obj.血液型名, DbType.String);
            DbParameter dbParam18 = _daoHelper.CreateParameter("@会社携帯", obj.会社携帯, DbType.String);
            DbParameter dbParam19 = _daoHelper.CreateParameter("@会社メールアドレス１", obj.会社メールアドレス１, DbType.String);
            DbParameter dbParam20 = _daoHelper.CreateParameter("@その他メールアドレス1", obj.その他メールアドレス1, DbType.String);
            /////////////////////////////////////////////////////////////////////////////////////////////////////
            DbParameter dbParam21 = _daoHelper.CreateParameter("@趣味", obj.趣味, DbType.String);
            DbParameter dbParam22 = _daoHelper.CreateParameter("@外国人登録番号", obj.外国人登録番号, DbType.String);
            DbParameter dbParam23 = _daoHelper.CreateParameter("@査証申請地", obj.査証申請地, DbType.String);
            DbParameter dbParam24 = _daoHelper.CreateParameter("@在留期限", obj.在留期限, DbType.DateTime);// co the truong nay sai
            DbParameter dbParam25 = _daoHelper.CreateParameter("@滞在予定期間", obj.滞在予定期間, DbType.String);
            /////////////////////////////////////////////////////////////////////////////////////////////////////
            DbParameter dbParam26 = _daoHelper.CreateParameter("@パスポート番号", obj.パスポート番号, DbType.String);
            DbParameter dbParam27 = _daoHelper.CreateParameter("@発行日", obj.発行日, DbType.DateTime);
            DbParameter dbParam28 = _daoHelper.CreateParameter("@有効期限", obj.有効期限, DbType.DateTime);
            DbParameter dbParam29 = _daoHelper.CreateParameter("@ID番号1", obj.ID番号1, DbType.String);
            DbParameter dbParam30 = _daoHelper.CreateParameter("@ＩＤ番号発行日", obj.ＩＤ番号発行日, DbType.DateTime);
            /////////////////////////////////////////////////////////////////////////////////////////////////////
            DbParameter dbParam31 = _daoHelper.CreateParameter("@宗教", obj.宗教, DbType.String);
            DbParameter dbParam32 = _daoHelper.CreateParameter("@民族", obj.民族, DbType.String);
            DbParameter dbParam33 = _daoHelper.CreateParameter("@現住所1", obj.現住所1, DbType.String);
            DbParameter dbParam34 = _daoHelper.CreateParameter("@フランチャイズ", obj.フランチャイズ, DbType.String);
            DbParameter dbParam35 = _daoHelper.CreateParameter("@失踪日 ", obj.失踪日, DbType.DateTime);
            DbParameter dbParam36 = _daoHelper.CreateParameter("@死亡日", obj.死亡日, DbType.DateTime);

            DbCommand dbcommand = _daoHelper.CreateCommand("sp_frmJ_StaffBasicInfoInput_Update_Phuong", CommandType.StoredProcedure,
        dbParam1, dbParam2, dbParam3, dbParam4, dbParam5, dbParam6, dbParam7, dbParam8, dbParam9, dbParam10, dbParam11, dbParam12, dbParam13, dbParam37, dbParam14, dbParam38, dbParam15, dbParam16,
        dbParam17, dbParam18, dbParam19, dbParam20, dbParam21, dbParam22, dbParam23, dbParam24, dbParam25, dbParam26, dbParam27, dbParam28, dbParam29, dbParam30, dbParam31, dbParam32, dbParam33, dbParam34, dbParam35, dbParam36);
        
            _daoHelper.Execute(dbcommand);
        }
        public DataTable Insert_基本情報(基本情報 obj)
        {
            DbParameter dbParam1 = _daoHelper.CreateParameter("@企業ID", obj.企業ＩＤ, DbType.Int32);
          
            DbParameter dbParam3 = _daoHelper.CreateParameter("@人コード", obj.人コード, DbType.String);
            DbParameter dbParam4 = _daoHelper.CreateParameter("@姓", obj.姓, DbType.String);
            DbParameter dbParam5 = _daoHelper.CreateParameter("@名", obj.名, DbType.String);
            //////////////////////////////////////////////////////////////////////////////////////////////////
            DbParameter dbParam6 = _daoHelper.CreateParameter("@フリガナ姓", obj.フリガナ姓, DbType.String);
            DbParameter dbParam7 = _daoHelper.CreateParameter("@フリガナ名", obj.フリガナ名, DbType.String);
            DbParameter dbParam8 = _daoHelper.CreateParameter("@英字姓", obj.英字姓, DbType.String);
            DbParameter dbParam9 = _daoHelper.CreateParameter("@英字名", obj.英字名, DbType.String);
            DbParameter dbParam10 = _daoHelper.CreateParameter("@旧漢字", (obj.旧漢字 == "") ? null : obj.旧漢字, DbType.String);
            /////////////////////////////////////////////////////////////////////////////////////////////////////
            DbParameter dbParam11 = _daoHelper.CreateParameter("@旧姓フリガナ", (obj.旧姓フリガナ == "") ? null : obj.旧姓フリガナ, DbType.String);
            DbParameter dbParam12 = _daoHelper.CreateParameter("@性別", obj.性別, DbType.Boolean);// co the truong nay sai
            DbParameter dbParam13 = _daoHelper.CreateParameter("@生年月日", obj.生年月日, DbType.DateTime);
            DbParameter dbParam39 = _daoHelper.CreateParameter("@年齢", obj.年齢, DbType.Int32);
            DbParameter dbParam14 = _daoHelper.CreateParameter("@写真", obj.写真);
            DbParameter dbParam40 = _daoHelper.CreateParameter("@国籍", obj.国籍, DbType.Int32);
            DbParameter dbParam15 = _daoHelper.CreateParameter("@出身地", obj.出身地, DbType.String);
            ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
            DbParameter dbParam16 = _daoHelper.CreateParameter("@血液型コード", obj.血液型コード, DbType.String);
            DbParameter dbParam17 = _daoHelper.CreateParameter("@血液型名", obj.血液型名, DbType.String);
            DbParameter dbParam18 = _daoHelper.CreateParameter("@会社携帯", obj.会社携帯, DbType.String);
            DbParameter dbParam19 = _daoHelper.CreateParameter("@会社メールアドレス１", obj.会社メールアドレス１, DbType.String);
            DbParameter dbParam20 = _daoHelper.CreateParameter("@その他メールアドレス1", (obj.その他メールアドレス1 == "") ? null : obj.その他メールアドレス1, DbType.String);
            /////////////////////////////////////////////////////////////////////////////////////////////////////
            DbParameter dbParam21 = _daoHelper.CreateParameter("@趣味", (obj.趣味 == "") ? null : obj.趣味, DbType.String);
            DbParameter dbParam22 = _daoHelper.CreateParameter("@外国人登録番号", (obj.外国人登録番号 == "") ? null : obj.外国人登録番号, DbType.String);
            DbParameter dbParam23 = _daoHelper.CreateParameter("@査証申請地", (obj.査証申請地 == "") ? null : obj.査証申請地, DbType.String);
            DbParameter dbParam24 = _daoHelper.CreateParameter("@在留期限", obj.在留期限, DbType.DateTime);// co the truong nay sai
            DbParameter dbParam25 = _daoHelper.CreateParameter("@滞在予定期間", (obj.滞在予定期間 == "") ? null : obj.滞在予定期間, DbType.String);
            /////////////////////////////////////////////////////////////////////////////////////////////////////
            DbParameter dbParam26 = _daoHelper.CreateParameter("@パスポート番号", (obj.パスポート番号 == "") ? null : obj.パスポート番号, DbType.String);
            DbParameter dbParam27 = _daoHelper.CreateParameter("@発行日", obj.発行日, DbType.DateTime);
            DbParameter dbParam28 = _daoHelper.CreateParameter("@有効期限", obj.有効期限, DbType.DateTime);
            DbParameter dbParam29 = _daoHelper.CreateParameter("@ID番号1", obj.ID番号1, DbType.String);
            DbParameter dbParam30 = _daoHelper.CreateParameter("@ＩＤ番号発行日", obj.ＩＤ番号発行日, DbType.DateTime);
            /////////////////////////////////////////////////////////////////////////////////////////////////////
            DbParameter dbParam31 = _daoHelper.CreateParameter("@宗教", (obj.宗教 == "") ? null : obj.宗教, DbType.String);
            DbParameter dbParam32 = _daoHelper.CreateParameter("@民族", (obj.民族 == "") ? null : obj.民族, DbType.String);
            DbParameter dbParam33 = _daoHelper.CreateParameter("@現住所1", obj.現住所1, DbType.String);
            DbParameter dbParam34 = _daoHelper.CreateParameter("@フランチャイズ", obj.フランチャイズ, DbType.String);
            /////////////////////////////////////////////////////////////////////////////////////////////////////
            DbParameter dbParam35 = _daoHelper.CreateParameter("@失踪日 ", obj.失踪日, DbType.DateTime);
            DbParameter dbParam36 = _daoHelper.CreateParameter("@死亡日", obj.死亡日, DbType.DateTime);
            DbParameter dbParam37 = _daoHelper.CreateParameter("@廃止区分", obj.廃止区分, DbType.Int16);
            DbParameter dbParam38 = _daoHelper.CreateParameter("@社員分類コード", obj.社員分類コード, DbType.Int32);

            DbCommand dbcommand = _daoHelper.CreateCommand("sp_frmJ_StaffBasicInfoInput_Insert_Phuong", CommandType.StoredProcedure,
        dbParam1, dbParam3, dbParam4, dbParam5, dbParam6, dbParam7, dbParam8, dbParam9, dbParam10, dbParam11, dbParam12, dbParam13, dbParam39, dbParam14, dbParam40, dbParam15, dbParam16,
        dbParam17, dbParam18, dbParam19, dbParam20, dbParam21, dbParam22, dbParam23, dbParam24, dbParam25, dbParam26, dbParam27, dbParam28, dbParam29, dbParam30, dbParam31, dbParam32, dbParam33, dbParam34, dbParam35, dbParam36, dbParam37, dbParam38);


            return _daoHelper.Query(dbcommand);
        }
        public DataTable Check人コード(String 人コード, Int32 @企業ＩＤ)
        {

            DbParameter dbParam2 = _daoHelper.CreateParameter("@人コード", 人コード, DbType.String);
            DbParameter dbParam3 = _daoHelper.CreateParameter("@ID", @企業ＩＤ, DbType.Int32);
            DbCommand dbcommand = _daoHelper.CreateCommand(CommandNameManager.sp_frmJ_StaffBasicInfoInput_Check人コード, CommandType.StoredProcedure, dbParam2, dbParam3);
            return _daoHelper.Query(dbcommand);
        }
        public DataTable CheckID番号(String ID番号, String 人コード)
        {
            DbParameter dbParam1 = _daoHelper.CreateParameter("@人コード", 人コード, DbType.String);
            DbParameter dbParam2 = _daoHelper.CreateParameter("@ID番号", ID番号, DbType.String);

            DbCommand dbcommand = _daoHelper.CreateCommand(CommandNameManager.sp_frmJ_StaffBasicInfoInput_CheckID番号, CommandType.StoredProcedure, dbParam1, dbParam2);
            return _daoHelper.Query(dbcommand);
        }


        public DataTable checkEmailExists(String tableName, String ID, String Email, String columnName, String columnID)
        {
            DbParameter param1 = _daoHelper.CreateParameter("@TableName", tableName, DbType.String);
            DbParameter param2 = _daoHelper.CreateParameter("@ID", ID, DbType.String);
            DbParameter param3 = _daoHelper.CreateParameter("@Email", Email, DbType.String);
            DbParameter param4 = _daoHelper.CreateParameter("@ColumnName", columnName, DbType.String);
            DbParameter param5 = _daoHelper.CreateParameter("@ColumnID", columnID, DbType.String);
            DbCommand command = _daoHelper.CreateCommand("checkEmailExists_luclt", CommandType.StoredProcedure, param1, param2, param3, param4, param5);
            return _daoHelper.Query(command);
        }


        #endregion

        #region Vhoat
        public DataTable select基本情報_Partial_59_Hoat()
        {
            DbCommand dbcommand = _daoHelper.CreateCommand(CommandNameManager.select基本情報_Partial_59_Hoat, CommandType.StoredProcedure);
            DataTable dt = _daoHelper.Query(dbcommand);
            return dt;
        }

        public DataTable select基本情報_with_企業ID_Partial_59_Hoat(string id)
        {
            DbCommand dbcommand = _daoHelper.CreateCommand(CommandNameManager.select基本情報_with_企業ID_Partial_59_Hoat, CommandType.StoredProcedure);
            DbParameter para = _daoHelper.CreateParameter("@企業ID", id);
            dbcommand.Parameters.Add(para);

            DataTable dt = _daoHelper.Query(dbcommand);
            return dt;
        }

        public int Update基本情報_Mode_38(基本情報 en)
        {
            /*
             * @人コード ,@姓 ,@名 ,@フリガナ姓 ,
             * @フリガナ名 ,@英字姓 ,@英字名 ,@旧姓漢字 ,@旧姓フリガナ ,@性別 ,@生年月日 ,
	         *   @写真 ,@国籍 ,@出身地 ,@血液型コード ,@血液型名 ,@会社携帯 ,@会社メールアドレス１ ,@会社メールアドレス２ ,
	         *  @趣味 ,@外国人登録番号 ,@在留期限 ,@滞在予定期間 ,@パスポート番号 ,@発行日 ,@有効期限 ,@市民権 
             * 
             * 
             */
            int i = 0;
            try
            {
                m_requestTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");

                DbParameter param1 = _daoHelper.CreateParameter("@人コード", en.人コード);
                DbParameter param2 = _daoHelper.CreateParameter("@姓", en.姓);
                DbParameter param3 = _daoHelper.CreateParameter("@名", en.名);
                DbParameter param4 = _daoHelper.CreateParameter("@フリガナ姓", en.フリガナ姓);
                DbParameter param5 = _daoHelper.CreateParameter("@フリガナ名", en.フリガナ名);
                DbParameter param6 = _daoHelper.CreateParameter("@英字姓", en.英字姓);
                DbParameter param7 = _daoHelper.CreateParameter("@英字名", en.英字名);
                DbParameter param8 = _daoHelper.CreateParameter("@旧姓漢字", en.旧漢字);
                DbParameter param9 = _daoHelper.CreateParameter("@旧姓フリガナ", en.旧姓フリガナ);
                DbParameter param10 = _daoHelper.CreateParameter("@性別", en.性別);
                DbParameter param11 = _daoHelper.CreateParameter("@生年月日", en.生年月日);
                DbParameter param12 = _daoHelper.CreateParameter("@写真", en.写真);
                DbParameter param13 = _daoHelper.CreateParameter("@国籍", en.国籍);
                DbParameter param14 = _daoHelper.CreateParameter("@出身地", en.出身地);
                DbParameter param15 = _daoHelper.CreateParameter("@血液型コード", en.血液型コード);
                DbParameter param16 = _daoHelper.CreateParameter("@血液型名", en.血液型名);
                DbParameter param17 = _daoHelper.CreateParameter("@会社携帯", en.会社携帯);
                DbParameter param18 = _daoHelper.CreateParameter("@会社メールアドレス１", en.会社メールアドレス１);
                DbParameter param19 = _daoHelper.CreateParameter("@会社メールアドレス２", en.会社メールアドレス２);
                DbParameter param20 = _daoHelper.CreateParameter("@趣味", en.趣味);
                DbParameter param21 = _daoHelper.CreateParameter("@外国人登録番号", en.外国人登録番号);
                DbParameter param22 = _daoHelper.CreateParameter("@在留期限", en.在留期限);
                DbParameter param23 = _daoHelper.CreateParameter("@滞在予定期間", en.滞在予定期間);
                DbParameter param24 = _daoHelper.CreateParameter("@パスポート番号", en.パスポート番号);
                DbParameter param25 = _daoHelper.CreateParameter("@発行日", en.発行日);
                DbParameter param26 = _daoHelper.CreateParameter("@有効期限", en.有効期限);
                DbParameter param27 = _daoHelper.CreateParameter("@市民権", en.市民権);
                DbParameter param28_1 = _daoHelper.CreateParameter("@企業ID", en.企業ＩＤ);
                DbParameter param28 = _daoHelper.CreateParameter("@社員ID", en.社員ID);

                DbCommand commands = _daoHelper.CreateCommand(CommandNameManager.sp_Update基本情報_Mode_38, CommandType.StoredProcedure);

                commands.Parameters.Add(param1);
                commands.Parameters.Add(param2);
                commands.Parameters.Add(param3);
                commands.Parameters.Add(param4);
                commands.Parameters.Add(param5);
                commands.Parameters.Add(param6);
                commands.Parameters.Add(param7);
                commands.Parameters.Add(param8);
                commands.Parameters.Add(param9);
                commands.Parameters.Add(param10);
                commands.Parameters.Add(param11);
                commands.Parameters.Add(param12);
                commands.Parameters.Add(param13);
                commands.Parameters.Add(param14);
                commands.Parameters.Add(param15);
                commands.Parameters.Add(param16);
                commands.Parameters.Add(param17);
                commands.Parameters.Add(param18);
                commands.Parameters.Add(param19);
                commands.Parameters.Add(param20);
                commands.Parameters.Add(param21);
                commands.Parameters.Add(param22);
                commands.Parameters.Add(param23);
                commands.Parameters.Add(param24);
                commands.Parameters.Add(param25);
                commands.Parameters.Add(param26);
                commands.Parameters.Add(param27);
                commands.Parameters.Add(param28_1);
                commands.Parameters.Add(param28);

                i = _daoHelper.Execute(commands);
                m_displayTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                ExportFile.WriteLog("", "", "UPDATE", "Update基本情報_Mode_38", m_requestTime, m_displayTime);

            }
            catch (Exception ex)
            {
                m_displayTime = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss");
                ExportFile.WriteErr("", "", "Update", ex.Message, m_requestTime, m_displayTime);


            }
            return i;
        }

        public DataTable sp_Select基本情報_MODE_UPDATE_39_Hoat(String id)
        {
            DbCommand dbcommand = _daoHelper.CreateCommand(CommandNameManager.sp_Select基本情報_MODE_UPDATE_39_Hoat, CommandType.StoredProcedure);
            DbParameter para = _daoHelper.CreateParameter("@社員ID", id);
            dbcommand.Parameters.Add(para);
            DataTable dt = _daoHelper.Query(dbcommand);
            return dt;
        }

        //Date: 29-12-2011
        public DataTable select基本情報_Partial_31_Hoat()
        {
            DbCommand dbcommand = _daoHelper.CreateCommand("select基本情報_Partial_31_Hoat", CommandType.StoredProcedure);
            DataTable dt = _daoHelper.Query(dbcommand);
            return dt;
        }

        public DataTable select基本情報_with_企業ID_Partial_31_Hoat(string id)
        {
            DbCommand dbcommand = _daoHelper.CreateCommand("select基本情報_with_企業ID_Partial_31_Hoat", CommandType.StoredProcedure);
            DbParameter para = _daoHelper.CreateParameter("@企業ID", id);
            dbcommand.Parameters.Add(para);

            DataTable dt = _daoHelper.Query(dbcommand);
            return dt;
        }

        public DataTable select基本情報_Mode_Update_31_Hoat(int id)
        {
            DbCommand dbcommand = _daoHelper.CreateCommand("select本社情報_Mode_Update_31_Hoat", CommandType.StoredProcedure);
            DbParameter para = _daoHelper.CreateParameter("@企業ID", id, DbType.Int32);
            dbcommand.Parameters.Add(para);
            DataTable dt = _daoHelper.Query(dbcommand);
            return dt;
        }

        //--------Date: 11/01/2011
        public bool checkExists企業ID_社員コードin基本情報_38(int 企業ID, string 社員コード)
        {
             DbCommand com = _daoHelper.CreateCommand("checkExists企業ID_社員コードin基本情報table_ORG38_VH", CommandType.StoredProcedure);
             DbParameter para1 = _daoHelper.CreateParameter("@企業ID", 企業ID);
             DbParameter para2 = _daoHelper.CreateParameter("@社員コード", 社員コード);
             com.Parameters.Add(para1); com.Parameters.Add(para2);
             DataTable dt = _daoHelper.Query(com);
             if (dt.Rows.Count > 0)
                  return true;
             else
                  return false;
        }

        public void get企業ID_In_基本情報_ORG38_Vh(out int 企業ID, int 社員ID)
        {
             int kq = 0;
             企業ID = 0;

             DbParameter para1 = _daoHelper.CreateParameter("@企業ID", 0);
             para1.Direction = ParameterDirection.Output;
             DbParameter para2 = _daoHelper.CreateParameter("@社員ID", 社員ID);

             DbCommand com = _daoHelper.CreateCommand("get企業ID_In_基本情報_ORG38_Vh", CommandType.StoredProcedure, para1, para2);
             
             kq = _daoHelper.Execute(com);
             企業ID = Int32.Parse(com.Parameters["@企業ID"].Value.ToString());
        }

        public string get_社員ID_In基本情報table_ORG38_VH(int 企業ID, string 社員コード)
        {
             DbCommand com = _daoHelper.CreateCommand("get_社員ID_In基本情報table_ORG38_VH", CommandType.StoredProcedure);
             DbParameter para1 = _daoHelper.CreateParameter("@企業ID", 企業ID);
             DbParameter para2 = _daoHelper.CreateParameter("@社員コード", 社員コード);
             com.Parameters.Add(para1); com.Parameters.Add(para2);
             DataTable dt = _daoHelper.Query(com);
             if (dt.Rows.Count > 0)
                  return dt.Rows[0][0].ToString();
             else
                  return "";
        }

        /// <summary>
        /// Coder: Ha Van Hoat
        /// Lay ra ngay sinh cua mot nhan vien ung voi ID 
        /// </summary>
        /// <param name="企業ID"></param>
        /// <returns></returns>
        public string getNgaySinh(int 企業ID)
        {
            DbParameter para = _daoHelper.CreateParameter("@企業ID", 企業ID);
            DbCommand com = _daoHelper.CreateCommand("getNgaySinh基本情報_Vh", CommandType.StoredProcedure, para);
            DataTable dt = _daoHelper.Query(com);
            
            if (dt.Rows.Count > 0)
                return dt.Rows[0][0].ToString();
            return "";
        }


        /// <summary>
        /// Check email is exist?
        /// </summary>
        /// <param name="emailName">email name</param>
        /// <param name="numberEmail">number email</param>
        /// <returns></returns>
        public bool CheckEmailExits_Org_38_Vh(string emailName, int numberEmail)
        {
            bool flag = false;
            
            DbParameter para1 = _daoHelper.CreateParameter("@emailName", emailName);
            DbParameter para2 = _daoHelper.CreateParameter("@numberEmail", numberEmail);
            DbCommand com = _daoHelper.CreateCommand("CheckEmailExits_Org_38_Vh", CommandType.StoredProcedure, para1, para2);

            DataTable dt = _daoHelper.Query(com);
            if (dt.Rows.Count > 0)
                flag = true;
            
            return flag;
        }

        /// <summary>
        /// Check email is same
        /// </summary>
        /// <param name="emailName"> email name</param>
        /// <param name="numberEmail">number email</param>
        /// <param name="empID">empid</param>
        /// <returns></returns>
        public bool CheckEmailSame_Org_38_Vh(string emailName, int numberEmail, int empID)
        {
            bool flag = false;

            DbParameter para1 = _daoHelper.CreateParameter("@emailName", emailName);
            DbParameter para2 = _daoHelper.CreateParameter("@numberEmail", numberEmail);
            DbParameter para3 = _daoHelper.CreateParameter("@empID", empID);

            DbCommand com = _daoHelper.CreateCommand("CheckEmailSame_Org_38_Vh ", CommandType.StoredProcedure, para1, para2,para3);

            DataTable dt = _daoHelper.Query(com);
            if (dt.Rows.Count > 0)
                flag = true;

            return flag;
        }



        #endregion


        public DataTable select基本情報_Mode_Update_59_Hoat(String id)
        {
            DbCommand dbcommand = _daoHelper.CreateCommand(CommandNameManager.select基本情報_Mode_Update_59_Hoat, CommandType.StoredProcedure);
            DbParameter para = _daoHelper.CreateParameter("@企業ID", id);
            dbcommand.Parameters.Add(para);
            DataTable dt = _daoHelper.Query(dbcommand);
            return dt;
        }

        

        #region //Thanh Dong
        public DataTable frmD_UserLoginManager_Select_企業コード_基本情報(string 企業コード, int 企業分類コード)
        {
            DbParameter dbparam = _daoHelper.CreateParameter("@企業コード", 企業コード, DbType.String);
            DbParameter dbparam1 = _daoHelper.CreateParameter("@企業分類コード", 企業分類コード, DbType.Int32);
            DbCommand dbcomman = _daoHelper.CreateCommand(CommandNameManager.sp_frmD_UserLoginManager_Select_企業コード_基本情報_Dong, CommandType.StoredProcedure, dbparam, dbparam1);
            return _daoHelper.Query(dbcomman);
        }

        public DataTable frmD_UserLoginManager_Search(string 企業コード, int 企業分類コード, string 人コード, string 姓名)
        {
            DbParameter dbparam1 = _daoHelper.CreateParameter("@企業コード", 企業コード, DbType.String);
            DbParameter dbparam2 = _daoHelper.CreateParameter("@企業分類コード", 企業分類コード, DbType.Int32);
            DbParameter dbparam3 = _daoHelper.CreateParameter("@人コード", 人コード, DbType.String);
            DbParameter dbparam4 = _daoHelper.CreateParameter("@姓名", 姓名, DbType.String);

            DbCommand dbcomman = _daoHelper.CreateCommand(CommandNameManager.sp_frmD_UserLoginManager_Search_Dong, CommandType.StoredProcedure, dbparam1, dbparam2, dbparam3, dbparam4);
            return _daoHelper.Query(dbcomman);
        }

        public DataTable frmD_Login_Get社員ID(int 企業ID, string 人コード)
        {
            DbParameter dbparam = _daoHelper.CreateParameter("@企業ID", 企業ID, DbType.Int32);
            DbParameter dbparam1 = _daoHelper.CreateParameter("@人コード", 人コード, DbType.String);
            DbCommand dbcomman = _daoHelper.CreateCommand(CommandNameManager.sp_frmD_Login_Get社員ID_Dong, CommandType.StoredProcedure, dbparam, dbparam1);
            return _daoHelper.Query(dbcomman);
        }

        #endregion
    }
}
